How to: Find and delete duplicate records in a table.
Solution:
Use the query wizard to find the duplicate records; then create a new table, set the duplicate records as primary fields, and append a query based on the original table to the copy.
1) If the Database window is not active, activate the Database window.
2) Use the query wizard to find the duplicate records:
a) Click the 'Queries' tab in the Database window.
Queries tab
b) Click 'New'. (The New Query dialog box appears.)
c) Select 'Find Duplicates Query Wizard' from the list box.
Find Duplicates Query Wizard
d) Click 'OK'. (The Find Duplicates Query Wizard appears.)
e) Select the table or query in which to search for duplicate fields from the list box.
Select table or query
NOTE: In the 'View' group, to select from a list of tables only, select the 'Tables' radio button. To select from a list of queries only, select the 'Queries' radio button. To select from a list of both tables and queries, select the 'Both' radio button.
View group
f) Click 'Next'.
g) Select the field(s) that might contain duplicate information:
1] Select the first field from the 'Available fields' list box.
Available fields
2] Click the '>' button. (The selected field moves to the Duplicate value list box.)
The '>' button
3] Repeat steps 2)g)1] and 2)g)2] for each field that might contain duplicate information.
h) Click 'Next'.
i) Select the field(s) to show in addition to those with duplicate entries:
NOTE: It is a good idea to show some other fields, to get an idea of how the duplicate fields relate to the rest of the table.
1] Select the first field from the 'Available fields' list box.
Available fields
2] Click the '>' button. (The selected field moves to the Additional query fields list box.)
The '>' button
3] Repeat steps 2)i)1] and 2)i)2] for each field to show.
j) Click 'Next'.
k) Type a name for the query in the 'What do you want to name your query' box.
Type a name
l) Select the 'View the results' radio button to view query results.
View the results
m) Click 'Finish'. (The query results appear in Datasheet view.)
3) Create a copy of the structure of the table:
a) If the Database window is not active, activate the Database window.
b) Click the 'Tables' tab in the Database window.
Tables tab
c) Select the desired table from the list box.
d) Select the 'Edit' menu and select 'Copy'. (The table is copied to the Clipboard.)
e) Select the 'Edit' menu and select 'Paste'. (The Paste Table As dialog box appears.)
f) Type a name for the copied table in the 'Table Name' box.
g) Select the 'Structure Only' radio button from the 'Paste Options' group.
h) Click 'OK'. (The copy appears on the tables list in the Database window.)
4) Open in the Design view the new structure table just created in step 3).
5) Select the field(s) that contain duplicates.
NOTE: These fields were found in step 2).
6) Select the 'Edit' menu and select 'Primary Key'. (A key symbol appears to the left of each field selected.)
NOTE: If a key symbol does not appear to the left of each field selected, select 'Primary Key' again.
7) Select the 'File' menu and select 'Save' to save the Table Design view changes.
8) Select the 'File' menu and select 'Close' to close the Table Design view.
9) Append the unique records to the new table:
a) If the Database window is not active, activate the Database window.
b) Click the 'Query' tab in the Database window.
Queries tab
c) Click 'New'. (The New Query dialog box appears.)
d) Select 'Design View' from the list box.
e) Click 'OK'. (The query opens in Design view, and the Show Table dialog box appears.)
f) Do one of the following:
1] Click the 'Tables' tab to display a list of tables only.
2] Click the 'Queries' tab to display a list of queries only.
3] Click the 'Both tab to display a list of both tables and queries
g) Select the table(s) and/or querie(s) to include in the query from the list box.
NOTE: To select more than one adjacent table or query, press and hold down SHIFT while selecting the tables or queries. To select more than one non-adjacent table or query, press and hold down CTRL while selecting the tables or queries.
h) Click 'Add'.
i) Click 'Close' when all of the desired tables and/or queries have been added.
j) Select the 'Query' menu and select 'Append...'. (The Append dialog box appears.)
k) In the 'Table Name' box in the 'Append To' group, type the name of the table to which to append the fields in the design grid.
l) Do one of the following:
1] If the table to which to append the data is in the open database, select the 'Current Database' radio button.
2] If the table to which to append the data is in a different database:
a] Select the 'Another Database' radio button.
b] Type the path and filename of the other database in the 'File Name' box.
m) Add the fields that are to be appended to another table to the query design grid:
1] To add all the fields in one table in the Query Design window:
a] Select the asterisk (*) in the desired table field list in the top half of the Query Design window.
b] Drag the asterisk (*) to the design grid in the bottom half of the Query Design window.
2] Select the desired field from a table field list in the top half of the Query Design window. (All the fields for that table are included in the append.)
Design grid
3] Drag the field to the desired column of the design grid in the bottom half of the Query Design window.
Design grid
4] Release the mouse button. (The field name appears in the Field row, and its table appears in the Table row.)
Field row and Table row
5] Repeat steps 9)m)1] through 9)m)4] for each field to append to another table.
NOTE: Fields can also be added by selecting a field from the drop-down list box in the 'Field' row of the design grid in the bottom half of the Query Design window.
Design grid
n) (Special Case) If the fields to be appended and the fields in the table to append to have different names:
1] Type the names of each corresponding field in the table appending to in the 'Append To' row.
2] Repeat step 9)n)1] for each field in the query design grid that has a different name than the field it should be appended to in the table appending to.
o) To preview the new table with the appended data BEFORE creating it, select the 'View' menu and select 'Datasheet'.
p) To create the new table with the appended data:
1] Make sure Query Design view is open.
NOTE: If Datasheet view is open, select the 'View' menu and select 'Query Design'.
2] Select the 'Query' menu and select 'Run'. (The table is created and is added to the Database window list of tables.)
q) To save the query:
1] Select the 'File' menu and select 'Save'. (The Save As dialog box appears.)
2] Type a name for the append query in the 'Query Name' box.
3] Click 'OK'.
r) Select the 'File' menu and select 'Close' to close the Query Design view.
10) Make sure the new table is correct.
11) Delete the original table.
12) Rename the new table to the original table's name.
NOTE: This table now contains no duplicate records.